--------------------------------------------------------
--  File created - Wednesday-July-02-2014   
--------------------------------------------------------
DROP PACKAGE "MAPADMIN"."APPLICATION";
DROP PACKAGE "MAPADMIN"."STATUSACTION";
DROP PACKAGE BODY "MAPADMIN"."APPLICATION";
DROP PACKAGE BODY "MAPADMIN"."STATUSACTION";

--------------------------------------------------------
--  DDL for Package APPLICATION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "MAPADMIN"."APPLICATION" AS

  procedure demo_proc;
  function demo_func(num1 in number) return number;

END APPLICATION;

/
--------------------------------------------------------
--  DDL for Package STATUSACTION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "MAPADMIN"."STATUSACTION" AS

  PROCEDURE demo;
  FUNCTION action_start(action VARCHAR2) RETURN NUMBER;
  FUNCTION action_next(case_id NUMBER, action VARCHAR2, note IN VARCHAR2, user_id VARCHAR2) RETURN NUMBER;

END STATUSACTION;

/
--------------------------------------------------------
--  DDL for Package Body APPLICATION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "MAPADMIN"."APPLICATION" AS

  PROCEDURE demo_proc AS
    merchantId MERCHANT.M_ID%TYPE;
  BEGIN
    merchantId := 1;
    select SEQ_MERCHANT.nextval into merchantId from dual;

    DBMS_OUTPUT.put_line('***' || merchantId);

  EXCEPTION
    WHEN others THEN
      DBMS_OUTPUT.put_line('Exception handling ...!');
  END demo_proc;


  function demo_func(num1 in number) return number as
    num2 number;
  begin
    num2 := 123;
    return num2;
  end;

END APPLICATION;

/
--------------------------------------------------------
--  DDL for Package Body STATUSACTION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "MAPADMIN"."STATUSACTION" AS

  PROCEDURE demo AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('***' || 'test');
  END;

  FUNCTION action_start(action VARCHAR2) RETURN NUMBER
  AS
    case_id CASEMAST.C_ID%TYPE;
    next_status STATUSMAST.S_NEXT%TYPE;
  BEGIN

    select s_next into next_status from statusmast
    where s_current = '_START_'
      and s_action = action;

    --select CASESEQ.NEXTVAL into case_id from dual;

    --insert into casemast(c_id, c_action, c_status, c_created_date, c_modified_date)
    --values(case_id, action, next_status, sysdate, sysdate);

    --return case_id;
    return 1;

  EXCEPTION
    WHEN OTHERS THEN
      return 0;
  END;

  FUNCTION action_next(case_id NUMBER, action VARCHAR2, note IN VARCHAR2, user_id VARCHAR2) RETURN NUMBER
  AS
    current_status STATUSMAST.S_CURRENT%TYPE;
    next_status STATUSMAST.S_NEXT%TYPE;
    nullable_note CASEHIST.CH_NOTE%TYPE;
  BEGIN

    nullable_note := note;
    if length(note) <= 0 then
      nullable_note := null;
    end if;

    select c_status into current_status
    from casemast
    where c_id = case_id;

    select s_next into next_status from statusmast
    where (s_current = current_status or s_current = '*')
      and s_action = action;

    if next_status = '*' then
      next_status := current_status;
    end if;

    insert into casehist(ch_case_id, ch_action, ch_status, ch_last_status,
      ch_note, ch_created_date, ch_created_by)
    values(case_id, action, next_status, current_status,
      nullable_note, sysdate, user_id);

    update casemast set c_action = action,
      c_status = next_status,
      c_modified_date = sysdate,
      c_modified_by = user_id
    where c_id = case_id;

    return case_id;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      return -1;
  END;

END STATUSACTION;

/
